package com.wwj.utils;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Auser on 2016/6/30.
 */
public class ExcelUtil {
    public final static String DATA_SET = "dataSet";
    public final static String NAME = "name";
    public final static String MODEL_PATH = "modePath";

    private static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

    public static Cell[] getExcleRow(File excelFile, int rowIndex) throws Exception {
        Workbook workbook = null;

        Cell[] row = null;
        try {
            workbook = Workbook.getWorkbook(excelFile);
            row = workbook.getSheet(0).getRow(rowIndex);
        } catch (Exception e) {
            logger.error(e.getLocalizedMessage());
        } finally {
            if (workbook != null) {
                workbook.close();
            }
        }
        return row;

    }


    public static List<Cell[]> getExcle(File excelFile) throws Exception {
        Workbook workbook = null;
        int count = 0;
        List<Cell[]> list = new ArrayList<Cell[]>();
        try {
            workbook = Workbook.getWorkbook(excelFile);
            Sheet sheet = workbook.getSheet(0);
            count = sheet.getRows();
            for (int i = 0; i < count; i++) {
                Cell[] row = sheet.getRow(i);
                list.add(row);
            }

        } catch (Exception e) {
            logger.error(e.getLocalizedMessage());
        } finally {
            if (workbook != null) {
                workbook.close();
            }
        }
        return list;

    }

    /**------------------2016.06.16------------------------*/

    /**
     * 适用于Strust2导出Excel
     *
     * @param response
     * @param list
     * @param name
     * @throws IOException
     */
    public static void createExcel(HttpServletResponse response, List<List<String>> list, String name) throws IOException {
        //构建response返回的excel数据流
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        workbook.setSheetName(0, "sheet1");
        for (int i = 0; i < list.size(); i++) {
            List<String> temp = list.get(i);
            HSSFRow row = sheet.createRow(i);
            for (int j = 0; j < temp.size(); j++) {
                HSSFCell cell = row.createCell(j);
                String cellValue = temp.get(j);
                if (i == 0) {
                    cell.setCellValue(cellValue);
                } else {
                    if (j < 3) {
                        cell.setCellValue(cellValue);
                    } else {
                        cell.setCellValue(Integer.valueOf(cellValue));
                    }
                }
            }
        }
        //
        response.setContentType("application/vnd.ms-excel");
        response.addHeader("Content-Disposition", "attachment; filename=" + new String((name).getBytes("gb2312"), "iso8859-1"));
        OutputStream fos = response.getOutputStream();
        workbook.write(fos);
        fos.flush();
        fos.close();
    }


    public static HSSFWorkbook createExcel(List<List<String>> list, String name) throws IOException {
        //构建response返回的excel数据流
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        workbook.setSheetName(0, "sheet1");
        for (int i = 0; i < list.size(); i++) {
            List<String> temp = list.get(i);
            HSSFRow row = sheet.createRow(i);
            for (int j = 0; j < temp.size(); j++) {
                HSSFCell cell = row.createCell(j);
                String cellValue = temp.get(j);
                if (i == 0) {
                    cell.setCellValue(cellValue);
                } else {
                    if (j < 3) {
                        cell.setCellValue(cellValue);
                    } else {
                        cell.setCellValue(Integer.valueOf(cellValue));
                    }
                }
            }
        }
        return workbook;
    }


    /**
     * 获取excel表头
     *
     * @param filePath
     * @return
     */
    public static HSSFWorkbook getModelWorkbook(String filePath) {
        File fi = new File(filePath);
        POIFSFileSystem fs = null;
        HSSFWorkbook wb = null;
        try {
            if (!fi.exists()) {
                System.out.println("模板文件:" + filePath + "不存在!");
                return null;
            }

            fs = new POIFSFileSystem(new FileInputStream(fi));
            wb = new HSSFWorkbook(fs);
            return wb;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

}
